package com.gdssoft.pdaweb.dao.am.impl;
/*
 *  捷达世软件（深圳）有限公司
 *  Copyright (C) 2008-2010 ShenZhen JieDaShi Software Limited Company.
*/
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.gdssoft.core.command.QueryFilter;
import com.gdssoft.core.dao.impl.BaseDaoImpl;
import com.gdssoft.core.util.DateUtil;
import com.gdssoft.pdaweb.dao.am.PdaAmMeterListDao;
import com.gdssoft.pdaweb.model.Excel;
import com.gdssoft.pdaweb.model.am.AmPlan;
import com.gdssoft.pdaweb.model.am.PdaAmMeter;
import com.gdssoft.pdaweb.model.am.PdaAmMeterList;
import com.gdssoft.pdaweb.model.water.PdaWaterList;


public class PdaAmMeterListDaoImpl extends BaseDaoImpl<PdaAmMeterList> implements PdaAmMeterListDao{

	public PdaAmMeterListDaoImpl() {
		super(PdaAmMeterList.class);
	}

	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
	
	/**
	 * sqlAndArgsForQueryOrCount 得到一致的用于查询记录数和记录的sql以及参数list
	 * @author F3229233 2013-1-15 下午3:21:50 
	 * @param pl
	 * @return Map(包含从from开始的sql及条件参数list)
	 */
	private Map<String,Object> sqlAndArgsForQueryOrCount(PdaAmMeterList pl){
		List<Object> args = new ArrayList<Object>();
		
		StringBuffer sql = new StringBuffer(" FROM PDA_AM_METER_LIST pl" +
			" JOIN PDA_AM_METER pm ON pl.AM_ID=pm.AM_ID" +
			" JOIN PDA_APARMENT pa ON pm.APARMENT_ID=pa.APARMENT_ID WHERE pl.MONTH=? ");
		
		String monthStr = pl.getMonth()==null?sdf.format(new Date()):pl.getMonth();
		pl.setMonth(monthStr);
		args.add(pl.getMonth());
		
		if(pl.getStatus()!=null && !pl.getStatus().isEmpty()){	//条件：状态
			if(pl.getStatus().equals("0")||pl.getStatus().equals("1")){
				sql.append("AND pl.STATUS=? ");
				args.add(pl.getStatus());
			}else{
				sql.append("AND pl.STATUS IN (2,3,4) ");
			}
		}
		if(pl.getAmId()!=null && !pl.getAmId().isEmpty()){	
			sql.append("AND pl.AM_ID LIKE ? ");
			args.add(pl.getAmId()==null?"%":pl.getAmId()+"%");
		}
		if(pl.getPdaAmMeter()!=null){
			if(pl.getPdaAmMeter().getClientId()!=null && !pl.getPdaAmMeter().getClientId().isEmpty()){
				sql.append("AND pm.CLIENT_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getClientId()+"%");
			}
			if(pl.getPdaAmMeter().getDistrictId()!=null && !pl.getPdaAmMeter().getDistrictId().isEmpty()){
				sql.append("AND pm.DISTRICT_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getDistrictId()+"%");
			}
			if(pl.getPdaAmMeter().getBuildingId()!=null && !pl.getPdaAmMeter().getBuildingId().isEmpty()){
				sql.append("AND pm.BUILDING_ID LIKE ? ");
				args.add(pl.getPdaAmMeter().getBuildingId()+"%");
			}
			if(pl.getPdaAmMeter().getAparmentId()!=null && !pl.getPdaAmMeter().getAparmentId().isEmpty()){
				sql.append("AND pm.APARMENT_ID LIKE ?");
				args.add(pl.getPdaAmMeter().getAparmentId()+"%");
			}
		}
		
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("SQL", sql);
		map.put("ARGS", args);
		return map;
	}
	
	@Override
	public int queryCount(PdaAmMeterList pl){
		Map sqlMap = sqlAndArgsForQueryOrCount(pl);
		String sql = "SELECT COUNT(1) " + sqlMap.get("SQL").toString();
		List<Object> args = (List<Object>)sqlMap.get("ARGS");		
		return jdbcTemplate.queryForInt(sql,args.toArray());					
	}
	
	@SuppressWarnings("rawtypes")
	@Override
	public List<PdaAmMeterList> query(PdaAmMeterList pl,int start,int to) {
		List<PdaAmMeterList> list = new ArrayList<PdaAmMeterList>();
		
		String monthStr = pl.getMonth()==null?sdf.format(new Date()):pl.getMonth();
		pl.setMonth(monthStr);
		
		Map sqlMap = sqlAndArgsForQueryOrCount(pl);
		
		String sql = "SELECT B.* FROM (SELECT ROWNUM rn, A.* FROM(" 
				+ "SELECT nvl(pll.DEGREE,-1) AS LASTDEGREE,pl.* FROM PDA_AM_METER_LIST pll RIGHT JOIN ("
				+ "SELECT pl.ID,pl.AM_ID,pl.DEGREE,pm.DISTRICT_ID,pm.BUILDING_ID,pm.APARMENT_ID,pa.NAME,pl.STATUS,pl.RECORDER_ID"
				+ sqlMap.get("SQL").toString()
				+") pl on pl.AM_ID = pll.AM_ID and pll.MONTH=?"
				+") A WHERE ROWNUM <= "+to+") B WHERE rn > "+start;

		List<Object> args = (List<Object>)sqlMap.get("ARGS");
		args.add(DateUtil.getLastMonthStr(monthStr));
		
		try{
			List rows = jdbcTemplate.queryForList(sql,args.toArray());
			Iterator it = rows.iterator();
			while(it.hasNext()) {
			    Map map = (Map) it.next();
			    PdaAmMeterList newpl = new PdaAmMeterList();
			    newpl.setPdaAmMeter(new PdaAmMeter());
			    newpl.setMonth(monthStr);
			    newpl.setId(((BigDecimal)map.get("ID")).longValue());
			    newpl.setAmId(map.get("AM_ID").toString());
			    newpl.setStatus(map.get("STATUS").toString());
			    newpl.setRecorderId(map.get("RECORDER_ID").toString());
//			    newpl.setLastDegree(((BigDecimal)map.get("LASTDEGREE")).longValue());
			    newpl.setDegree(((BigDecimal)map.get("DEGREE")).longValue());			    
			    newpl.getPdaAmMeter().setDistrictId(map.get("DISTRICT_ID").toString());
			    newpl.getPdaAmMeter().setBuildingId(map.get("BUILDING_ID").toString());
			    newpl.getPdaAmMeter().setAparmentId(map.get("APARMENT_ID").toString());
			    newpl.getPdaAmMeter().setAparmentName(map.get("NAME").toString());
			    list.add(newpl);
			}
		}catch(Exception e){
			logger.debug("PdaAmMeterList query error:"+e.getMessage());			
		}
		return list;
	}

	@Override
	public int importExcel(List<Excel> excelList){
	  try{
		List<Object> args = new ArrayList<Object>();
		int rowNum = 0;
		for(Excel excel : excelList)
		{
			args.add(excel.getCurDegree());
			args.add(excel.getId());
			args.add(excel.getCurMonth());
			
			StringBuffer sql = new StringBuffer("insert PDA_AM_METER_LIST set degree = ? " +
					"where am_id = ? and month = ?");
			
			try{
			   rowNum += jdbcTemplate.update(sql.toString(),args.toArray());
			  
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		System.out.println("导入的行数为："+rowNum);
		return rowNum;
		}
		catch(Exception e){
					
		}
		return 0;
	}
	@Override
	public boolean checkAmidExist(String amId,String depIdS){
		int ret = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM PDA_AM_METER pl WHERE pl.AM_ID = '"+amId+"' and type = '2' and pl.DEPID in ("+depIdS+")" );
		if(ret > 0){
			return true;
		}
		return false;
	}

	@Override
	public boolean recodExist(String amId, String importMonth){
		String sql = "SELECT COUNT(*) FROM PDA_AM_METER_LIST pl WHERE pl.AM_ID = '"+amId+
				"' AND pl.MONTH = '"+importMonth+"'";
		int ret = jdbcTemplate.queryForInt(sql);
		if(ret > 0)
		{
			return true;
		}
		return false;
	}
	
	@Override
	public Long getDegreeByTime(String amId,String month){
		String sql = "select nvl(degree,-1)degree from PDA_AM_METER_LIST where am_id = '"+amId+"' and month = '"+month+"'";
		long degree =-1l;
		try{
			List rows = jdbcTemplate.queryForList(sql);
			Iterator it = rows.iterator();
			while(it.hasNext()) {
			    Map map = (Map) it.next();
			    degree=Long.parseLong(map.get("degree").toString());
			   
			}
		}catch(Exception e){
			logger.debug("查询出错");			
		}
		return degree;
	}
	

	@Override
	public boolean check(String ids, String status) {
		int count = jdbcTemplate.update("UPDATE PDA_AM_METER_LIST SET STATUS = "+status +" WHERE ID IN ("+ids+")");
		if(count==ids.split(",").length){
			return true;
		}
		return false;
	}
	
	@Override
	public PdaAmMeterList getAmListByMeterIDAndMonth(String meterID, String month){
		String hql="from PdaAmMeterList where amId=? and month=?";
		return (PdaAmMeterList) this.findUnique(hql,new String[]{meterID, month});
	}

	@Override
	public boolean saveFixedUsage(PdaAmMeterList pdaAmMeterList) {
		String sql = "";
		if(pdaAmMeterList.getRemark().equals("")){
			sql = "update pda_am_meter_list set status=0, fixedusage="
					+pdaAmMeterList.getFixedUsage()
					+" where id="
					+pdaAmMeterList.getId();
		}else{
			sql = "update pda_am_meter_list set status=0, remark='"
					+ pdaAmMeterList.getRemark()
					+ "', fixedusage="
					+pdaAmMeterList.getFixedUsage()
					+" where id="
					+pdaAmMeterList.getId();
		}
		
		int count = jdbcTemplate.update(sql);
		if(count > 0){
			return true;
		}
		return false;
	}

	@Override
	public List<AmPlan> getPlanList(String month, String planName,
			String executor,String dptId,QueryFilter filter) {
		// TODO Auto-generated method stub
       List<AmPlan> list = new ArrayList<AmPlan>();
		
		String sql =" SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT T.PLAN_ID, T.PLAN_NAME, T.STATUSP, T.EXECUTOR" 
		           +	"  FROM (SELECT P.PLAN_ID,"  
		           + 	"               P.PLAN_NAME,"  
		           + 	"               P.ACCOUNT_DAY," 
		           + 	"               P.METER_COUNT,"  
		           + 	"               P.PLAN_STATUS,"  
		           + 	"               P.CREATEDDT,"  
		           + 	"               P.CREATEDBY,"  
		           + 	"               P.LASTEDITDT,"  
		           + 	"               P.LASTEDITBY," 
		           + 	"               U.STATUSP,"  
		           + 	"               (SELECT DISTINCT Z.EXECUTOR"  
		           + 	"                  FROM PDA_AM_METER_MONTHPLAN Z"  
		           + 	"                 WHERE Z.MONTH = ?"  
		           + 	"                   AND Z.PLAN_ID = P.PLAN_ID) EXECUTOR"  
		           + 	"          FROM PDA_AM_METER_PLAN P,"  
		           + 	"               (SELECT DISTINCT Q.PLAN_ID, MAX(S.STATUS) STATUSP"  
		           + 	"                  FROM PDA_PLAN_DETAIL Q, PDA_AM_METER_LIST S,PDA_AM_METER W"  
		           + 	"                 WHERE Q.METER_ID = S.AM_ID AND W.AM_ID=S.AM_ID "  
		           + 	"                   AND W.DEPID IN ("+dptId+")AND S.MONTH = ?"  
		           + 	"                   AND S.STATUS <> '1'"  
		           + 	"                 GROUP BY Q.PLAN_ID) U"  
		           + 	"         WHERE P.PLAN_ID IN (U.PLAN_ID) ORDER BY P.PLAN_ID) T"  
		           + 	" WHERE T.PLAN_NAME LIKE '%' || ? || '%'"  
		           + 	"   AND T.EXECUTOR LIKE '%' || ? || '%')row_ WHERE  "
		           +    "   rownum <= "+(filter.getPagingBean().getFirstResult()+filter.getPagingBean().getPageSize())+") "
		           +    " WHERE rownum_ > "+filter.getPagingBean().getFirstResult()+"";

              String[] param=new String[]{month,month,planName,executor};
		
		try{
			List rows = jdbcTemplate.queryForList(sql,param);
			Iterator it = rows.iterator();
			while(it.hasNext()) {
			    Map map = (Map) it.next();
			    AmPlan newpl = new AmPlan();
			    newpl.setPlanId(Long.parseLong(map.get("PLAN_ID").toString()));//計劃ID
			    newpl.setPlanName(map.get("PLAN_NAME").toString());//計劃名稱
			    newpl.setPlanStatus(Long.parseLong(map.get("STATUSP").toString()));//計劃表狀態，抓計劃表下所有電表狀態級別最高的
			    newpl.setExecutor(map.get("EXECUTOR").toString());//抄表人
			    newpl.setLastEditBy(month);//由於AmPlan沒有抄表年月，暫時用這個欄位存抄表年月			   
			    list.add(newpl);
			}
		}catch(Exception e){
			logger.debug("getPlanList query error:"+e.getMessage());			
		}
		return list;
	}
	@Override
	public boolean checkPlan(String ids, String status,String month) {
		String sql="UPDATE PDA_AM_METER_LIST" 
				  +"     SET STATUS = '"+status+"'"  
				  +"   WHERE ID IN ("+ids+")";
		int count = jdbcTemplate.update(sql);
		if(count>0){
			return true;
		}
		return false;
	}
	
	@Override
	public String getPdaAmMeterIdList(String ids, String month) {
		// TODO Auto-generated method stub
		String idsParam="";
		String sql="SELECT DISTINCT S.ID"  
				  +"                   FROM PDA_PLAN_DETAIL Q, PDA_AM_METER_LIST S" 
				  +"                 WHERE Q.METER_ID = S.AM_ID" 
				  +"                   AND S.MONTH = '"+month+"'"  
				  +"                   AND S.STATUS <> '1'"  
				  +"                   AND Q.PLAN_ID IN ("+ids+")";
		 try{
				List rows = jdbcTemplate.queryForList(sql);
				Iterator it = rows.iterator();
				while(it.hasNext()) {
				    Map map = (Map) it.next();
				    if(idsParam.equals("")){
				    	idsParam=map.get("ID").toString();
				    }else{
				    	idsParam=idsParam+","+map.get("ID").toString();
				    }
				   
				}
			}catch(Exception e){
				logger.debug("getPdaAmMeterIdList query error:"+e.getMessage());			
			}

		return idsParam;
	}

	@Override
	public List<PdaAmMeterList> getCheckDetailSearch(String planId,
			String planName, String month, String executor, String fangId,
			String waterId, String dongId, String quId,String depId,QueryFilter filter) {
		Long planIdl=Long.parseLong(planId);
		// TODO Auto-generated method stub
		String sql=	"SELECT p"
				  +	"  FROM PdaAmMeterList AS p, PdaPlanDetail AS q"
				  + " WHERE q.meterId = p.amId"
				  + "   AND q.planId ="+planIdl+" AND p.status<>'1'"
				  + "   AND p.month='"+month+"'" 
				  + "   AND p.pdaAmMeter.districtId LIKE '%'||'"+quId+"'||'%'"
				  + "   AND p.pdaAmMeter.buildingId LIKE '%'||'"+dongId+"'||'%'"
				  + "   AND p.pdaAmMeter.aparmentId LIKE '%'||'"+fangId+"'||'%'"
				  + "   AND p.amId LIKE '%'||'"+waterId+"'||'%' "
				  + "   AND p.pdaAmMeter.department.depId IN ("+depId+")";
		 Query query=this.getSession().createQuery(sql);
		 query.setFirstResult(filter.getPagingBean().getFirstResult());
		 query.setMaxResults(filter.getPagingBean().getPageSize());
	return query.list();
	}

	@Override
	public int getCheckDetailSearchNumb(String planId,
			String planName, String month, String executor, String fangId,
			String waterId, String dongId, String quId,String depId) {
		Long planIdl=Long.parseLong(planId);
		// TODO Auto-generated method stub
		String sql=	"SELECT p"
				  +	"  FROM PdaAmMeterList AS p, PdaPlanDetail AS q"
				  + " WHERE q.meterId = p.amId"
				  + "   AND q.planId ="+planIdl+" AND p.status<>'1'"
				  + "   AND p.month='"+month+"'" 
				  + "   AND p.pdaAmMeter.districtId LIKE '%'||'"+quId+"'||'%'"
				  + "   AND p.pdaAmMeter.buildingId LIKE '%'||'"+dongId+"'||'%'"
				  + "   AND p.pdaAmMeter.aparmentId LIKE '%'||'"+fangId+"'||'%'"
				  + "   AND p.amId LIKE '%'||'"+waterId+"'||'%' "
				  + "   AND p.pdaAmMeter.department.depId IN ("+depId+")";
		 
	return this.findByHql(sql).size();
	}

	@Override
	public List<PdaAmMeterList> checkStatus(Long planId, String month) {
		List<PdaAmMeterList> amList =new ArrayList<PdaAmMeterList>();
		String sql="SELECT q.ID\n" +
				"  FROM PDA_PLAN_DETAIL p, PDA_AM_METER_LIST q  " + 
				"  WHERE p.METER_ID = q.AM_ID    " +   
				"   AND p.PLAN_ID = "+planId+"  "  +
				"   AND q.MONTH = '"+month+"' "    + 
				"   AND q.STATUS = '2' ";

			 try{
					List rows = jdbcTemplate.queryForList(sql);
					Iterator it = rows.iterator();
					while(it.hasNext()) {
					    Map map = (Map) it.next();
					    PdaAmMeterList newpl = new PdaAmMeterList();
			//		    newpl.getVersion()(planId); 
			//		    newpl.setLastEditBy(month);
					    newpl.setMonth(month);
					    amList.add(newpl);
					}
				}catch(Exception e){
					logger.debug("getPdaWaterIdList query error:"+e.getMessage());			
				}
		
		return amList;
	}

	@Override
	public int getPlanListNum(String month, String planName, String executor,
			String dptId) {
	     	int numb=0;
			String sql =" SELECT COUNT(T.PLAN_ID) NUMB" 
			           +	"  FROM (SELECT P.PLAN_ID,"  
			           + 	"               P.PLAN_NAME,"  
			           + 	"               P.ACCOUNT_DAY," 
			           + 	"               P.METER_COUNT,"  
			           + 	"               P.PLAN_STATUS,"  
			           + 	"               P.CREATEDDT,"  
			           + 	"               P.CREATEDBY,"  
			           + 	"               P.LASTEDITDT,"  
			           + 	"               P.LASTEDITBY," 
			           + 	"               U.STATUSP,"  
			           + 	"               (SELECT DISTINCT Z.EXECUTOR"  
			           + 	"                  FROM PDA_AM_METER_MONTHPLAN Z"  
			           + 	"                 WHERE Z.MONTH = ?"  
			           + 	"                   AND Z.PLAN_ID = P.PLAN_ID) EXECUTOR"  
			           + 	"          FROM PDA_AM_METER_PLAN P,"  
			           + 	"               (SELECT DISTINCT Q.PLAN_ID, MAX(S.STATUS) STATUSP"  
			           + 	"                  FROM PDA_PLAN_DETAIL Q, PDA_AM_METER_LIST S,PDA_AM_METER W"  
			           + 	"                 WHERE Q.METER_ID = S.AM_ID AND W.AM_ID=S.AM_ID "  
			           + 	"                   AND W.DEPID IN ("+dptId+")AND S.MONTH = ?"  
			           + 	"                   AND S.STATUS <> '1'"  
			           + 	"                 GROUP BY Q.PLAN_ID) U"  
			           + 	"         WHERE P.PLAN_ID IN (U.PLAN_ID)) T"  
			           + 	" WHERE T.PLAN_NAME LIKE '%' || ? || '%'"  
			           + 	"   AND T.EXECUTOR LIKE '%' || ? || '%'";

	              String[] param=new String[]{month,month,planName,executor};
			
			try{
				List rows = jdbcTemplate.queryForList(sql,param);
				Iterator it = rows.iterator();
				while(it.hasNext()) {
				    Map map = (Map) it.next();
				     numb=Integer.parseInt(map.get("NUMB").toString());
				  
				}
			}catch(Exception e){
				logger.debug("getPlanList query error:"+e.getMessage());			
			}
			return numb;
	}

	@Override
	public List<PdaAmMeterList> getList(Map params,String depIds) {
			 Query query = getQuery4getList( params, depIds);
			 query.setFirstResult(Integer.parseInt((String)params.get("start")));
			 query.setMaxResults(Integer.parseInt((String)params.get("limit")));
		return query.list();
	}

	@Override
	public int getListCount(Map params,String depIds) {
		return getQuery4getList(params, depIds).list().size();
	}
	
	//for function  getList(HttpServletRequest req,String depIds) and getListCount(HttpServletRequest req,String depIds)
	private Query getQuery4getList(Map params, String depIds) {
		String ss = (String)params.get("Q_status_S_SIN");
		String hql="SELECT p FROM PdaAmMeterList AS p"
				  + " WHERE p.month=:month" 
				  + "   AND p.pdaAmMeter.districtId LIKE :quId"
				  + "   AND p.pdaAmMeter.buildingId LIKE :dongId"
				  + "   AND p.pdaAmMeter.aparmentId LIKE :fangId"
				  + "   AND p.amId LIKE :amId"
				  + "   AND p.pdaAmMeter.department.depId IN ("+depIds+")";	
		
		String degree = "";
		if(ss!=null){
			hql += "   AND p.status in ("+ss+")";
			hql += "   AND p.degree <> :degree";
			degree = (String)params.get("Q_degree_L_NEQ");
		}else{
			hql += "   AND p.degree = :degree";
			degree = (String)params.get("Q_degree_L_EQ");
		}
		
		return this.getSession().createQuery(hql)
				 .setParameter("amId", "%"+(String)params.get("Q_amId_S_LK")+"%")
				 .setParameter("month", (String)params.get("Q_month_S_LK"))
				 .setParameter("degree", Long.parseLong(degree))
				 .setParameter("quId", "%"+(String)params.get("Q_pdaAmMeter.districtId_S_LK")+"%")
				 .setParameter("dongId", "%"+(String)params.get("Q_pdaAmMeter.buildingId_S_LK")+"%")
				 .setParameter("fangId", "%"+(String)params.get("Q_pdaAmMeter.aparmentId_S_LK")+"%");
	}
}
